--EXEC SP_BOM_INVENTORY_REPORT 1062, '0'
ALTER PROC SP_BOM_INVENTORY_REPORT( @SREACH_PROCESSSTEP_ID INT, @DEFAULT_PROMPT VARCHAR(20) )
AS
BEGIN
	DECLARE @PROCESS_STEPS_LOCATION TABLE( ProcessStep_ID INT, Location_ID INT )
	DECLARE @PRODUCT_CURSOR AS CURSOR
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @LOCATION_ID INT
	DECLARE @QTY_IN INT

	DECLARE @RESULTS_TABLE TABLE ( 	ProcessStep_ID INT,
					Product_ID INT,
					Part_Number VARCHAR(50),
					Location_Name VARCHAR(50),
					Location_ID INT,
					Date_In DATETIME,
					Qty_In INT)

	INSERT INTO @PROCESS_STEPS_LOCATION 
	SELECT * FROM (
	SELECT DISTINCT ProcessStep_ID,Lot_Table.Location_ID from Lot_Table, Location WHERE 
	Lot_Table.Location_ID=Location.Location_id 
	UNION 
	SELECT DISTINCT ProcessStep_ID,PSLT.Location_ID from Process_Step_Location_Table PSLT , Location WHERE 
	PSLT.Location_id=Location.Location_id ) AS SQL_TABLE

	IF( @SREACH_PROCESSSTEP_ID = '0' )
	BEGIN
		INSERT INTO @RESULTS_TABLE 
		SELECT P.ProcessStep_ID, Product_ID, Part_Number, L.L_Vendor_Name AS Location_Name, L.Location_ID AS Location_ID, NULL, NULL
		FROM Product P, Process_Step PS, @PROCESS_STEPS_LOCATION PSL, Location L 
		WHERE P.ProcessStep_ID = PS.ProcessStep_ID AND P.ProcessStep_ID = PSL.ProcessStep_ID AND PSL.Location_ID  = L.Location_ID AND P_Parent_Product_ID IS NOT NULL AND P_Type = 'BOM' ORDER BY Part_Number
	END
	ELSE
	BEGIN
		INSERT INTO @RESULTS_TABLE 
		SELECT P.ProcessStep_ID, Product_ID, Part_Number, L.L_Vendor_Name AS Location_Name, L.Location_ID AS Location_ID, NULL, NULL
		FROM Product P, Process_Step PS, @PROCESS_STEPS_LOCATION PSL, Location L 
		WHERE P.ProcessStep_ID = @SREACH_PROCESSSTEP_ID AND P.ProcessStep_ID = PS.ProcessStep_ID AND P.ProcessStep_ID = PSL.ProcessStep_ID AND PSL.Location_ID  = L.Location_ID AND P_Parent_Product_ID IS NOT NULL AND P_Type = 'BOM' ORDER BY Part_Number
	END
	
	SET @PRODUCT_CURSOR = CURSOR FOR SELECT ProcessStep_ID, Location_ID FROM @RESULTS_TABLE
	OPEN @PRODUCT_CURSOR
	FETCH NEXT FROM @PRODUCT_CURSOR INTO @PROCESSSTEP_ID, @LOCATION_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @QTY_IN = 0
		--PRINT ' @PROCESSSTEP_ID = ' + CONVERT(VARCHAR,@PROCESSSTEP_ID)
		SELECT @QTY_IN = ISNULL( @QTY_IN, 0) + ISNULL( IL.IL_QTY_IN, 0 ) FROM LOT_TABLE LT, INVENTORY_LOT IL 
		WHERE LT.LOT_ID = IL.LOT_ID AND LT.LOT_CURRENT_STATUS = 'RUNNING' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOCATION_ID = @LOCATION_ID
		
		UPDATE @RESULTS_TABLE SET Qty_In = @QTY_IN FROM @RESULTS_TABLE WHERE ProcessStep_ID = @PROCESSSTEP_ID AND Location_ID = @LOCATION_ID
		
		SET @QTY_IN = 0
		FETCH NEXT FROM @PRODUCT_CURSOR INTO @PROCESSSTEP_ID, @LOCATION_ID
	END
	CLOSE @PRODUCT_CURSOR
	DEALLOCATE @PRODUCT_CURSOR

	--SELECT ProcessStep_ID, Product_ID, Part_Number, SUM( Qty_In ) FROM @RESULTS_TABLE GROUP BY ProcessStep_ID, Product_ID, Part_Number ORDER BY Part_Number
	SELECT * FROM @RESULTS_TABLE ORDER BY Part_Number
END